package com.yuyou.fn.timer.task;


import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.IJobHandler;
import com.xxl.job.core.handler.annotation.JobHander;
import com.yuyou.fn.common.service.IEmailService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

@JobHander(value="CheckDataTask")
@Component
public class CheckDataTask extends IJobHandler {

    @Value("${dev.env}")
    private boolean dev;

    @Resource
    private IEmailService emailService;

    @Resource
    private DataSource dataSource;


    @Override
    public ReturnT<String> execute(String... strings) throws Exception {

        String evn = dev ? "测试环境" : "生产环境" ;

        String notifyTo = "wenzuojing1@zy.com" ;

        if( count("SELECT count(*)  from yyfn_report.student_reg_detail where  reg_id not in( SELECT reg_id FROM yyfn.student_reg )") > 1 ){
            emailService.sendEmail( notifyTo , String.format("【%s】%s" , evn  , "student_reg_detail has more data") , "empty" , null , null  );
        }

        if( count("select count(*)  from `yyfn`.`student_reg` a left join `yyfn_report`.student_reg_detail b on a.`reg_id` = b.`reg_id` and a.`reg_status` = b.`reg_status` and a.`charging_status` = b.`charging_status` where b.`reg_id` is null") > 1 ){
            emailService.sendEmail(notifyTo , String.format("【%s】%s" , evn  , "student_reg_detail 数据不一致") , "empty" , null , null  );
        }

        if( !dev && count("select count(*) from yyfn.`change_record` a left join  `yyfn_report`.`consume_record` b on  a.`tracking_id` =  b.`reg_id` and  b.`consume_record_id` like CONCAT(a.change_record_id , '%')   where   a.subject = 'real_income' and a.category_id = 10500 and a.create_time > '2018-02-01 00:00:00'  and b.reg_id is null ") > 4 ){
            emailService.sendEmail(notifyTo , String.format("【%s】%s" , evn  , "consume_record 数据不一致") , "empty" , null , null  );
        }

        if( count("select count(*) from yyfn_report.`merge_student_reg_detail`") != count("select count(*) from ( select student_id, class_id    from yyfn.student_reg where reg_status in (0,3)  group by student_id , class_id ) a")){
            emailService.sendEmail(notifyTo , String.format("【%s】%s" , evn  , "merge_student_reg_detail 数据不一致") , "empty" , null , null  );
        }



        update("UPDATE yyfn.pay_record set update_time  = date_add(update_time , interval 1 second ) where create_time > date_add(now() , interval -1 day ) ;");

        return ReturnT.SUCCESS;
    }

    private long count(String sql) throws SQLException {
        Connection connection = null ;
        Statement statement = null ;
        ResultSet resultSet = null ;
        try{
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if(resultSet.next()){
                return resultSet.getLong(1);
            }
        }finally {
            if(resultSet != null ){
                resultSet.close();
            }
            if(statement != null ){
                statement.close();
            }
            if(connection != null ){
                connection.close();
            }
        }
        return 0l ;
    }


    private void update(String sql) throws SQLException {
        Connection connection = null ;
        Statement statement = null ;
        try{
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            statement.execute(sql);
        }finally {
            if(statement != null ){
                statement.close();
            }
            if(connection != null ){
                connection.close();
            }
        }
    }
}
